﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Salon
{
    class CarService
    {
        public CarService() { }

        public Car saveCar(Car car)
        {
            String insertQuery = "INSERT INTO Car(mark,type,color,year,cost,price,salon,discount,sold) VALUES(@mark,@model,@color,@year,@cost,@price,@salon,@discount,@sold)  SELECT SCOPE_IDENTITY() ";

            String updateQuery = "UPDATE Car SET mark=@mark,type=@model,color=@color,year=@year,cost=@cost,price=@price,salon=@salon,discount=@discount WHERE id=@id";

            SqlCommand cmd = SqlService.getInstance().getCommand( (car.Id < 1) ? insertQuery : updateQuery );
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mark", car.Mark.Id);
            cmd.Parameters.AddWithValue("@model", car.Model.Id);
            cmd.Parameters.AddWithValue("@color", car.Color);
            cmd.Parameters.AddWithValue("@year", car.Year);
            cmd.Parameters.AddWithValue("@cost", car.Cost);
            cmd.Parameters.AddWithValue("@price", car.Price);
            cmd.Parameters.AddWithValue("@salon", car.Salon.Id);
            cmd.Parameters.AddWithValue("@discount", car.Discount);
            cmd.Parameters.AddWithValue("@sold", car.Sold);
            if (car.Id > 0)
            {
                cmd.Parameters.AddWithValue("@id", car.Id);
                cmd.ExecuteNonQuery();
            }
            else
            {
                car.Id = Convert.ToInt32( cmd.ExecuteScalar() );

            }
            

            return car;
        }

        //przy usuwaniu usuwamy a nie ustawiamy flagę active na false
        public void deleteCar(Car Car)
        {
            String query = "DELETE FROM Car WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", Car.Id);
            cmd.ExecuteNonQuery();

            // można by ustawiamy flagę active na false gdy ma powiązania!
        }
        // ustawianie flagi: active
        public void setActive(int car_id, bool Active)
        {
            String query = "UPDATE Car SET active=@active WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", car_id);
            cmd.Parameters.AddWithValue("@active", Active);
            cmd.ExecuteNonQuery();
        }
        // dodawanie do zamówienia
        public void addToOrder(Car Car, int order_id)
        {
            //removeFromOrder(Car,order_id); // usunięcie jeśli był na zamówieniu
            //String query = "UPDATE Order_cars SET order_id=@order_id WHERE car_id=@car_id";
            String query = "INSERT INTO Order_cars (order_id,car_id) VALUES (@order_id,@car_id) ";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@car_id", Car.Id);
            cmd.Parameters.AddWithValue("@order_id", order_id);
            cmd.ExecuteNonQuery();

            setActive(Car.Id, false);
        }
        // usuwanie z zamówienia
        public void removeFromOrder(Car Car)
        {
            String query = "DELETE FROM Order_cars WHERE car_id = @car_id ";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@car_id", Car.Id);
            cmd.ExecuteNonQuery();

            setActive(Car.Id,true);
        }

        public Car getCarById(int id)
        {
            String query = "SELECT * from Car WHERE id=@id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", id);
            SqlDataReader rdr = cmd.ExecuteReader();
            if (!rdr.HasRows)
                return null;
            rdr.Read();

            Car Car = new Car();
            Car.Id = rdr.GetInt32(rdr.GetOrdinal("id"));
            Car.Mark = ServiceFactory.getDictService().getMarkById(rdr.GetInt32(rdr.GetOrdinal("mark")));
            Car.Model = ServiceFactory.getDictService().getModelById(rdr.GetInt32(rdr.GetOrdinal("type")));
            Car.Color = rdr.GetInt32(rdr.GetOrdinal("color"));
            Car.Year = rdr.GetInt32(rdr.GetOrdinal("year"));
            Car.Cost = rdr.GetFloat(rdr.GetOrdinal("cost"));
            Car.Price = rdr.GetFloat(rdr.GetOrdinal("price"));
            Car.Discount = rdr.GetFloat(rdr.GetOrdinal("discount"));
            Car.Sold = rdr.GetBoolean(rdr.GetOrdinal("sold"));
            Car.Salon = ServiceFactory.getAutoSalonService().getAutoSalonById(rdr.GetInt32(rdr.GetOrdinal("salon")));

            return Car;
        }
        public IList<Car> getCarsByOrderId(int id)
        {
            IList<Car> Cars = new List<Car>();
            String query = "SELECT * FROM Car JOIN Order_cars ON id=car_id WHERE order_id=@order_id";
            SqlCommand cmd = SqlService.getInstance().getCommand(query);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@order_id", id);
            SqlDataReader rdr = cmd.ExecuteReader();
            
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    Car Car = new Car();
                    Car.Id = rdr.GetInt32(rdr.GetOrdinal("id"));
                    Car.Mark = ServiceFactory.getDictService().getMarkById(rdr.GetInt32(rdr.GetOrdinal("mark")));
                    Car.Model = ServiceFactory.getDictService().getModelById(rdr.GetInt32(rdr.GetOrdinal("type")));
                    Car.Color = rdr.GetInt32(rdr.GetOrdinal("color"));
                    Car.Year = rdr.GetInt32(rdr.GetOrdinal("year"));
                    Car.Cost = rdr.GetFloat(rdr.GetOrdinal("cost"));
                    Car.Price = rdr.GetFloat(rdr.GetOrdinal("price"));
                    Car.Discount = rdr.GetFloat(rdr.GetOrdinal("discount"));
                    Car.Sold = rdr.GetBoolean(rdr.GetOrdinal("sold"));
                    Car.Salon = ServiceFactory.getAutoSalonService().getAutoSalonById(rdr.GetInt32(rdr.GetOrdinal("salon")));

                    Cars.Add(Car);
                }
            }

            rdr.Close();

            return Cars;
        }

        /// <summary>
        /// Zwraca listę obiektów typu Car na podstawie listy kontrolek filtrujących
        /// </summary>
        /// <param name="filterList"></param>
        /// <returns></returns>
        public IList<Car> getCars(IList<IFilter> filterList)
        {
            if (filterList == null) // Jeśli nie podano filtra to tylko aktywne nie sprzedane
            {
                Filter f = new Filter("sold", 0);
                filterList = f.asList();
                filterList.Add( new Filter("active",1) );
            }

            IList<Car> Cars = new List<Car>();
            String query = "SELECT * FROM Car WHERE id > 0 ";
            query += Global.getConditionString(filterList);

            SqlDataReader rdr = SqlService.getInstance().getCommand(query).ExecuteReader();
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    Car Car = new Car();
                    Car.Id = rdr.GetInt32(rdr.GetOrdinal("id"));
                    Car.Mark = ServiceFactory.getDictService().getMarkById(rdr.GetInt32(rdr.GetOrdinal("mark")));
                    Car.Model = ServiceFactory.getDictService().getModelById(rdr.GetInt32(rdr.GetOrdinal("type")));
                    Car.Color = rdr.GetInt32(rdr.GetOrdinal("color"));
                    Car.Year = rdr.GetInt32(rdr.GetOrdinal("year"));
                    Car.Cost = rdr.GetFloat(rdr.GetOrdinal("cost"));
                    Car.Price = rdr.GetFloat(rdr.GetOrdinal("price"));
                    Car.Discount = rdr.GetFloat(rdr.GetOrdinal("discount"));
                    Car.Sold = rdr.GetBoolean(rdr.GetOrdinal("sold"));
                    Car.Salon = ServiceFactory.getAutoSalonService().getAutoSalonById(rdr.GetInt32(rdr.GetOrdinal("salon")));

                    Cars.Add(Car);
                }
            }


            rdr.Close();

            return Cars;
        }

        public string genName(Car car)
        {
            return "#" + car.Id + " " + car.Mark + " " + car.Model + " (" + car.Year + ")";
        }
    }
}
